#
# This file is a part of the open-eBackup project.
# This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0.
# If a copy of the MPL was not distributed with this file, You can obtain one at
# http://mozilla.org/MPL/2.0/.
#
# Copyright (c) [2024] Huawei Technologies Co.,Ltd.
#
# THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
# EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
# MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
#

import json
import os
import re
import socket
import time
from datetime import datetime, timezone
from pathlib import Path

from common.parse_parafile import get_env_variable
from saporacle.comm.common_util import remove_anypath, run_subprocess
from saporacle.comm.constants import SAPOracleConsts, SAPOracleErrorCode, SAPOracleFileStructure
from saporacle.comm.logger import logger
from saporacle.comm.saporacle_exception import SAPOracleException


class SAPOracle:

    """SAP on Oracle 主机管理"""

    def __init__(self, oracle_sid: str, sapsid: str, source_oracle_sid: str, source_sapsid: str, db_user: str = "",
                 db_pwd: str = "") -> None:
        self._oracle_sid = oracle_sid
        self._sapsid = sapsid
        self._source_oracle_sid = source_oracle_sid
        self._source_sapsid = source_sapsid
        self._db_user = db_user
        self._db_pwd = db_pwd
        self.oracle_sid = oracle_sid
        self.source_oracle_sid = source_oracle_sid
        self.source_sapsid = source_sapsid

        os.chdir(self._brtools_location())
        os.environ["ORACLE_SID"] = self._oracle_sid

    """host"""

    @staticmethod
    def host_name() -> str:
        return socket.gethostname()

    @staticmethod
    def cpu_count() -> int:
        return os.cpu_count()

    @staticmethod
    def timestamp_to_date(timestamp: int) -> str:
        """将 Unix 时间戳转换为当前时区日期时间。"""
        date = datetime.fromtimestamp(timestamp).strftime('%Y-%m-%d %H.%M.%S')
        logger.info(f"timestamp: {timestamp} to date: {date}")
        return date

    @staticmethod
    def sap_environments() -> dict:
        """
        :raise SAPOracleException:
        """
        keys = ["ORACLE_SID", "ORACLE_HOME", "SAPDATA_HOME"]
        environments = {k: os.environ.get(k) for k in keys}
        non_exists = [k for k in keys if not environments[k]]
        if non_exists:
            logger.error(f"No environment variable. {non_exists}")
            raise SAPOracleException(SAPOracleErrorCode.ENVIRONMENTS_NOT_SET, str(non_exists))
        return environments

    def sapdata_home(self) -> str:
        """
        :raise SAPOracleException:
        """
        return self.sap_environments()["SAPDATA_HOME"]

    def saparch_dir(self) -> str:
        """
        :raise SAPOracleException:
        """
        path = os.environ.get("SAPARCH")
        if path:
            logger.info("Use SAPARCH in environment variables.")
            return path
        return os.path.join(self.sapdata_home(), "saparch")

    def saparch_summary_log_file(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.saparch_dir(),
                            SAPOracleFileStructure.BRARCHIVE_SUMMARY_LOG_FILE.format(ORACLE_SID=self._oracle_sid))


    def sapbackup_dir(self) -> str:
        """
        :raise SAPOracleException:
        """
        path = os.environ.get("SAPBACKUP")
        if path:
            logger.info("Use SAPBACKUP in environment variables.")
            return path
        return os.path.join(self.sapdata_home(), "sapbackup")

    def sapbackup_summary_log_file(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.sapbackup_dir(),
                            SAPOracleFileStructure.BRBACKUP_SUMMARY_LOG_FILE.format(ORACLE_SID=self._oracle_sid))

    def sapceorg_dir(self) -> str:
        """
        :raise SAPOracleException:
        """
        path = os.environ.get("SAPREORG")
        if path:
            logger.info("Use SAPREORG in environment variables.")
            return path
        return os.path.join(self.sapdata_home(), "sapreorg")

    def sapceorg_summary_log_file(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.sapceorg_dir(),
                            SAPOracleFileStructure.BRSPACE_SUMMARY_LOG_FILE.format(ORACLE_SID=self._oracle_sid))


    def control_file_dirs(self) -> list[str]:
        """
        :raise SAPOracleException:
        """
        _dirs = [
            os.path.join(self.sapdata_home(), "origlogA", "cntrl"),
            os.path.join(self.sapdata_home(), "origlogB", "cntrl"),
            os.path.join(os.environ.get("SAPDATA1") or os.path.join(self.sapdata_home(), "sapdata1"), "cntrl"),
            os.path.join(os.environ.get("SAPDATA2") or os.path.join(self.sapdata_home(), "sapdata2"), "cntrl")
        ]
        _dirs = [_dir for _dir in _dirs if os.path.isdir(_dir)]
        return _dirs

    def control_file_files(self) -> list[str]:
        """
        :raise SAPOracleException:
        """
        _dirs = [
            os.path.join(self.sapdata_home(), "origlogA", "cntrl",
                         SAPOracleFileStructure.CONTROL_FILES_WITH_FILE_NAME.format(ORACLE_SID=self._oracle_sid)),
            os.path.join(self.sapdata_home(), "origlogB", "cntrl",
                         SAPOracleFileStructure.CONTROL_FILES_WITH_FILE_NAME.format(ORACLE_SID=self._oracle_sid)),
            os.path.join(os.environ.get("SAPDATA1") or os.path.join(self.sapdata_home(), "sapdata1"), "cntrl",
                         SAPOracleFileStructure.CONTROL_FILES_WITH_FILE_NAME.format(ORACLE_SID=self._oracle_sid)),
            os.path.join(os.environ.get("SAPDATA2") or os.path.join(self.sapdata_home(), "sapdata2"), "cntrl",
                         SAPOracleFileStructure.CONTROL_FILES_WITH_FILE_NAME.format(ORACLE_SID=self._oracle_sid))
        ]
        _dirs = [_dir for _dir in _dirs if os.path.isdir(_dir)]
        return _dirs

    def database_dir(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.sap_environments()["ORACLE_HOME"], "database")

    def database_sap_profile(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.database_dir(),
                            SAPOracleFileStructure.BRTOOLS_PROFILE_FILE.format(ORACLE_SID=self._oracle_sid))

    def database_ora_profile(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.database_dir(),
                            SAPOracleFileStructure.ORACLE_PROFILE_FILE.format(ORACLE_SID=self._oracle_sid))

    def database_ora_spfile(self) -> str:
        """
        :raise SAPOracleException:
        """
        return os.path.join(self.database_dir(),
                            SAPOracleFileStructure.ORACLE_SPFILE_FILE.format(ORACLE_SID=self._oracle_sid))

    """database"""

    def login(self) -> None:
        """
        :raise SAPOracleException:
        """
        inp = f"{self._db_user}/{self._db_pwd}\n"
        logger.info(f"Login database by {self._db_user} start.")
        process = run_subprocess("sqlplus", inp=inp)
        if "Connected to:" in process.stdout:
            logger.info("Login database successfully.")
            return
        err_map = {
            "ORA-01017": SAPOracleErrorCode.DB_USERPWD_WRONG,
            "ORA-01031": SAPOracleErrorCode.INSUFFICIENT_USER_PRIVILEGE,
            "ORA-01109": SAPOracleErrorCode.DATABASE_OFFLINE,
            "ORA-01034": SAPOracleErrorCode.DATABASE_OFFLINE,
            # ORA-01033: ORACLE initialization or shutdown in progress
            "ORA-01033": SAPOracleErrorCode.DATABASE_OFFLINE,
            "ORA-00020": SAPOracleErrorCode.ERROR_ORACLE_OVER_MAX_LINK
        }
        match = re.search(r"(ORA-\d+): (.+)", process.stdout)
        if match and match.group(1) in err_map:
            logger.error(f"ORACLE Error output while login database. {process.stdout}")
            raise SAPOracleException(err_map[match.group(1)])
        else:
            logger.error(f"Unknown Oracle error output while login database. {process.stdout}")
            raise SAPOracleException(SAPOracleErrorCode.ORACLE_ERROR)

    def user_has_sufficient_privilege(self) -> None:
        """
        :raise SAPOracleException:
        """
        sql = ("SELECT user_role_privs.USERNAME FROM user_role_privs "
               "INNER JOIN user_sys_privs "
               "ON user_role_privs.USERNAME=user_sys_privs.USERNAME "
               "INNER JOIN v$pwfile_users "
               "ON user_role_privs.USERNAME=v$pwfile_users.USERNAME "
               "WHERE user_role_privs.GRANTED_ROLE='DBA' "
               "AND user_sys_privs.PRIVILEGE='CREATE SESSION' "
               "AND v$pwfile_users.SYSOPER='TRUE';")
        data = self._query_data(sql)
        if not data or data[0][0].upper() != self._db_user.upper():
            logger.error(f"Cannot prove user {self._db_user} has sufficient privilege.")
            raise SAPOracleException(SAPOracleErrorCode.INSUFFICIENT_USER_PRIVILEGE)
        logger.info(f"User {self._db_user} has sufficient privileges.")

    def system_information(self) -> dict:
        """
        :raise SAPOracleException:
        """
        tool = self._brtools_location() / "brspace.exe"
        command = f"{tool} -c force -l E -o detail,time -s 20 -u / -f dbshow -c dbstate"
        process = run_subprocess(command)
        find = (s for s in re.split(r"BR\d{4}[IWE] .+", process.stdout) if re.search(r"^Name\s+Value", s, re.MULTILINE))
        cont = next(find, "")
        if not cont:
            logger.error(f"Cannot match database system information content. {process.stdout}.")
            raise SAPOracleException(SAPOracleErrorCode.SUBPROCESS_EXCEPTION, command, "UnknownOutput")
        information = {match[0]: match[1].strip() for match in re.findall(r"^(\S+)\s+(.+)$", cont, re.MULTILINE)}
        [information.pop(k, None) for k in ["Name", "command_line"]]
        information["oracle_version"] = information.get("oracle_info", "").split(" ")[1]
        information["brtools_version"] = self.brtools_version()
        return information

    def scns_to_timestamps(self, scns: list[int]) -> list[int]:
        """将 scn 转换为 Unix 时间戳。
        :raise SAPOracleException:
        """
        field = ("(CAST(FROM_TZ(SCN_TO_TIMESTAMP({}), SESSIONTIMEZONE) AT TIME ZONE 'UTC' AS DATE) "
                 "- DATE '1970-01-01') * 86400 AS TS")
        sql = f"SELECT {','.join([field.format(scn) for scn in scns])} FROM v$database;"
        data = self._query_data(sql)
        try:
            timestamps = [int(d) for d in data[0]]
        except Exception as e:
            logger.error(f"Exception occurred while calculating scn timestamp. {scns} {sql} {e}", exc_info=1)
            raise SAPOracleException(SAPOracleErrorCode.UNKNOWN_ERROR)
        logger.info(f"scns: {scns} to timestamps: {timestamps}")
        return timestamps

    def archive_current_redolog(self) -> None:
        """
        :raise SAPOracleException:
        """
        sql = "alter system archive log current;"
        _ = self._query_data(sql, is_query=False)
        logger.info("Archived current redolog.")

    """brtools"""

    def all_brtools_exist(self) -> None:
        """
        :raise SAPOracleException:
        """
        paths = (self._brtools_location() / tool
                 for tool in ["brbackup.exe", "brarchive.exe", "brrestore.exe", "brspace.exe", "brtools.exe"])
        if not all(path.exists() for path in paths):
            logger.error("Not all brtools exist.")
            raise SAPOracleException(SAPOracleErrorCode.BRTOOLS_NOT_EXIST)
        logger.info("All brtools exist.")

    def brtools_version(self) -> str:
        """
        :raise SAPOracleException:
        """
        tool = self._brtools_location() / "brtools.exe"
        command = f"{tool} -V"
        process = run_subprocess(command)
        match = re.search(r"BRTOOLS (.+)", process.stdout)
        if not match:
            logger.error(f"Cannot match brtools version. {process.stdout}")
            raise SAPOracleException(SAPOracleErrorCode.SUBPROCESS_EXCEPTION, command, "UnknownOutput")
        version = match.group(1).strip()
        logger.info(f"Brtools version: {version}")
        return version

    def dump_brtools_outfile(self) -> None:
        """
        :raise SAPOracleException:
        """
        file = self._brtools_outfile()
        if not file.exists():
            return
        ids = [get_env_variable(k) for k in ["pid", "job_id", "sub_job_id"]]
        dumpfile = Path(SAPOracleConsts.LOG_PATH, SAPOracleConsts.BRTOOLS_LOG)
        if dumpfile.exists() and dumpfile.stat().st_size >= 10 * 1024 * 1024:
            dumpfile.write_bytes(dumpfile.read_bytes()[5 * 1024 * 1024:])
        with dumpfile.open("a", encoding="utf-8") as f:
            f.write(f"\n{'-'*96}{ids} head{'-'*96}\n")
            f.write(file.read_text(encoding="utf-8"))
            f.write(f"\n{'-'*96}{ids} end{'-'*96}\n")
        remove_anypath(file)

    """backint"""

    def is_backint_installed(self) -> bool:
        src_files = (Path(SAPOracleConsts.BACKINT_PATH, file) for file in os.listdir(SAPOracleConsts.BACKINT_PATH))
        dst_files = (self._brtools_location() / file for file in os.listdir(SAPOracleConsts.BACKINT_PATH))
        return all(dst.is_symlink() and dst.resolve() == src and src.exists() for src, dst in zip(src_files, dst_files))

    def install_backint(self) -> None:
        """
        :raise SAPOracleException:
        """
        try:
            for file in os.listdir(SAPOracleConsts.BACKINT_PATH):
                src = Path(SAPOracleConsts.BACKINT_PATH, file)
                dst = self._brtools_location() / file
                if dst.is_symlink() and dst.resolve() == src.absolute():
                    continue
                if dst.exists() or dst.is_symlink():
                    remove_anypath(dst)
                dst.symlink_to(src)
        except Exception as e:
            logger.error(f"Exception occurred while installing backint. {e}", exc_info=1)
            raise SAPOracleException(SAPOracleErrorCode.UNKNOWN_ERROR)
        logger.info("Install backint succeeded.")

    def write_backint_param_file(self, param: dict) -> None:
        logger.info(f"Backint param: {param}")
        self._backint_param_file().write_text(json.dumps(param), encoding="utf-8")

    def remove_backint_param_file(self) -> None:
        """
        :raise SAPOracleException:
        """
        remove_anypath(self._backint_param_file())

    """private"""

    def _backint_param_file(self) -> Path:
        return Path(SAPOracleConsts.TMP_PATH,
                    SAPOracleConsts.BACKINT_PARAM_FILE.format(oracle_sid=self._source_oracle_sid))

    def _brtools_location(self) -> Path:
        return Path(SAPOracleConsts.BRTOOLS_LOCATION.format(sapsid=self._sapsid))

    def _brtools_outfile(self) -> Path:
        return Path(SAPOracleConsts.TMP_PATH,
                    SAPOracleConsts.BRTOOLS_OUTFILE.format(oracle_sid=self._source_oracle_sid))

    def _query_data(self, sql: str, is_query: bool = True) -> list[list[str]]:
        """
        :raise SAPOracleException:
        """
        command = f"sqlplus {self._db_user}/"
        inp = (f"\"{self._db_pwd}\"\n"
               "set pagesize 50000;\n"
               "set linesize 32767;\n"
               "col Name format a50;\n"
               "set space 1;\n"
               "set feedback on;\n"
               "set colsep '|';\n"
               f"{sql}")
        process = run_subprocess(command, inp)
        if not is_query:
            return []
        if "no rows selected" in process.stdout:
            return []
        match = re.search(r"^(\d+) rows* selected", process.stdout, re.MULTILINE)
        if not match:
            logger.error(f"Cannot match rows number. {process.stdout}")
            raise SAPOracleException(SAPOracleErrorCode.UNKNOWN_ERROR)
        num = int(match.group(1))
        cont = re.split(r"^-+.+\n", process.stdout, flags=re.MULTILINE)[1]
        rows = cont.splitlines()[:num]
        data = [re.split(r"\s*\|\s*", row) for row in rows]
        return data

